insert overwrite table jms_dm.dm_customer_deliver_trend_dt partition (dt)
select networktype                                                                 --区分代理,加盟,网点
     , timetype                                                                    --时间类型
     , date_time                                                                   --天
     , agent_code                                                                  --代理区code
     , agent_name                                                                  --代理区
     , fran_code                                                                   --加盟商code
     , fran_name                                                                   --加盟商名称
     , network_code                                                                --网点编码
     , network_name                                                                --网点名称
     , customer_level                                                              --客户级别
     , total_customer_level                                                        --等级对应的客户数
     , round((total_customer_level - before_total_customer_level) / before_total_customer_level,
             4)                                            as ring_customer_level  --环比,和前一天比较
     , round(total_customer_level / total_customer_num, 4) as ratio_customer_level --客户数据占比	 等级对应的客户数/总客户数
     , round(total_ticket_level / total_ticket_num, 4)     as ratio_ticket_level   --件量占比	     等级对应的票数/总票数
     , round((total_ticket_level / total_ticket_num) / (total_customer_level / total_customer_num),
             4)                                            as rate_ticket_customer --件量占比/客户数据占比
     , dorisdt
     , dorisdt                                             as dt
from (
         select t3.timetype
              , t3.networktype
              , t3.date_time                                                                                                                                    --日期
              , t3.dorisdt
              , t3.agent_name                                                                                                                                   --代理区
              , t3.agent_code                                                                                                                                   --代理区code
              , t3.fran_name                                                                                                                                    --加盟商
              , t3.fran_code                                                                                                                                    --加盟商code
              , t3.network_code                                                                                                                                 --网点编码
              , t3.network_name                                                                                                                                 --网点名称
              , t3.customer_level                                                                                                                               --客户级别
              , t3.total_customer_num                                                                                                                           --总客户数
              , t3.total_ticket_num                                                                                                                             --总票数
              , lag(total_customer_level)
                    over (partition by timetype,networktype,agent_code,fran_code,network_code,customer_level order by date_time) as before_total_customer_level --上一条等级对应的客户数
              , t3.total_customer_level                                                                                                                         --等级对应的客户数
              , t3.total_ticket_level                                                                                                                           --等级对应的票数
         from (--计算天,代理,客户级别维度下的  客户数和票数
                  select timetype
                       , networktype
                       , date_time                                    --日期
                       , dorisdt
                       , agent_name                                   --代理区
                       , agent_code                                   --代理区code
                       , fran_name                                    --加盟商
                       , fran_code                                    --加盟商code
                       , network_code                                 --网点编码
                       , network_name                                 --网点名称
                       , customer_level                               --客户级别
                       , total_customer_num                           --总客户数
                       , total_ticket_num                             --总票数
                       , count(customer_code) as total_customer_level --等级对应的客户数
                       , sum(ticket_quantity) as total_ticket_level   --等级对应的票数
                  from jms_dm.dm_customer_date_network_level_ticket_detail_dt t2
                  group by timetype
                         , networktype
                         , date_time          --日期
                         , dorisdt
                         , agent_name         --代理区
                         , agent_code         --代理区code
                         , fran_name          --加盟商
                         , fran_code          --加盟商code
                         , network_code       --网点编码
                         , network_name       --网点名称
                         , customer_level     --客户级别
                         , total_customer_num --总客户数
                         , total_ticket_num --总票数
              ) t3
     ) t4
     where substr(dorisdt, 1, 7) = substr('{{ execution_date | cst_ds }}',1,7)
       or dorisdt = next_day(date_add('{{ execution_date | cst_ds }}',-1*7), 'Monday')
distribute by dt
;
